#coding=utf-8
from uliweb import expose
from sqlalchemy import *
from uliweb.orm import *
from sqlalchemy.sql import select
from sqlalchemy.sql import and_, or_, not_
import datetime
from datetime import date
from uliweb.contrib.auth import require_login
from views import *

def __begin__():
    from uliweb import function
    return function('require_login')()

def get_count_view_graph(groupby):
    from uliweb.orm import get_model
    from uliweb.core.html import Tag
    
    begin_Date,end_Date = get_date()
    #初始化data,
    data = {}
    tx = get_model('tx')
    kfzx = get_model('kfzx')
    sys = get_model('system')
    #stages = ['11','12','13','14','15','16','17','18','19','1A','1B','21','22','23','24','25','26','31','32','33','34']
    stages = ['new_ky','new_ss','new_wc','new_yc','new_zt','old_ky','old_ss','old_wc','old_yc','old_zt','total']
    if groupby == 'tx':
        for obj in tx.all():
            data[obj.id] ={}
            for y in stages:
                data[obj.id][y] = 0
    
    if groupby == 'zx':
        for obj in kfzx.all():
            data[obj.id] ={}
            for y in stages:
                data[obj.id][y] = 0
            
    if groupby == 'sys':
        for obj in sys.all():
            data[obj.id] ={}
            data[obj.id]['kfzx']=obj.kfzx.id
            for y in stages:
                data[obj.id][y] = 0
                
    req = get_model('requirement')
    if groupby == 'tx':
        new = select([req.c.tx,req.c.stage,func.count(req.c.id)],and_(req.c.created_date>=begin_Date,req.c.created_date<=end_Date)).group_by(req.c.tx,req.c.stage)
        #old = select([req.c.tx,req.c.stage,func.count(req.c.id)],and_(req.c.created_date<begin_Date,req.c.begin_date>=begin_Date)).group_by(req.c.tx,req.c.stage)
        old = select([req.c.tx,req.c.stage,func.count(req.c.id)],or_(and_(req.c.created_date<begin_Date,req.c.modified_date<begin_Date,~req.c.stage.in_(['19','1A','FN'])),and_(req.c.created_date<begin_Date,req.c.modified_date<=end_Date,req.c.modified_date>=begin_Date))).group_by(req.c.tx,req.c.stage)
    if groupby == 'zx':
        new = select([sys.c.kfzx,req.c.stage,func.count(req.c.id)],and_(req.c.created_date>=begin_Date,req.c.created_date<=end_Date,req.c.main_sys==sys.c.id)).group_by(sys.c.kfzx,req.c.stage)
        #old = select([sys.c.kfzx,req.c.stage,func.count(req.c.id)],and_(req.c.created_date<begin_Date,req.c.begin_date>=begin_Date,req.c.main_sys==sys.c.id)).group_by(sys.c.kfzx,req.c.stage)
        old = select([sys.c.kfzx,req.c.stage,func.count(req.c.id)],or_(and_(req.c.created_date<begin_Date,req.c.modified_date<begin_Date,~req.c.stage.in_(['19','1A','FN']),req.c.main_sys==sys.c.id),and_(req.c.created_date<begin_Date,req.c.modified_date<=end_Date,req.c.modified_date>=begin_Date,req.c.main_sys==sys.c.id))).group_by(sys.c.kfzx,req.c.stage)
    if (request.user.department=='2')and(groupby == 'sys'):
        i = request.user._kfzx_
        new = select([req.c.main_sys,req.c.stage,func.count(req.c.id)],
            and_(req.c.created_date>=begin_Date,
            req.c.created_date<=end_Date,
            req.c.main_sys==sys.c.id,
            sys.c.kfzx==i))\
            .group_by(req.c.main_sys,req.c.stage)
        old = select([req.c.main_sys,req.c.stage,func.count(req.c.id)],
            or_(and_(req.c.created_date<begin_Date,
            req.c.modified_date<begin_Date,
            ~req.c.stage.in_(['19','1A','FN']),
            req.c.main_sys==sys.c.id,
            sys.c.kfzx==i),
            and_(req.c.created_date<begin_Date,
            req.c.modified_date<=end_Date,
            req.c.modified_date>=begin_Date,
            req.c.main_sys==sys.c.id,
            sys.c.kfzx==i)))\
            .group_by(req.c.main_sys,req.c.stage)        
    elif groupby == 'sys':
        new = select([req.c.main_sys,req.c.stage,func.count(req.c.id)],and_(req.c.created_date>=begin_Date,req.c.created_date<=end_Date)).group_by(req.c.main_sys,req.c.stage)
        old = select([req.c.main_sys,req.c.stage,func.count(req.c.id)],or_(and_(req.c.created_date<begin_Date,req.c.modified_date<begin_Date,~req.c.stage.in_(['19','1A','FN'])),and_(req.c.created_date<begin_Date,req.c.modified_date<=end_Date,req.c.modified_date>=begin_Date))).group_by(req.c.main_sys,req.c.stage)

    new_rs = new.execute()
    old_rs = old.execute()   
         
    for row in new_rs:
        a, stage, count = row
        stage = settings.PARA.new_map[stage]        
        data[a][stage] += count
        data[a]['total'] += count

    for row in old_rs:
        a, stage, count = row
        stage = settings.PARA.old_map[stage]
        data[a][stage] += count
        data[a]['total'] += count
    
    x = []
    if groupby == 'tx':
        for t, v in data.items():                    
            v['tx_name'] = tx.get(t).name
            x.append(v)

    if groupby == 'zx':
        for t, v in data.items():        
            v['zx_name'] = kfzx.get(t).name
            x.append(v)
    
    if groupby == 'sys':
        for t, v in data.items():        
            v['sys_name'] = sys.get(t).name
            if v['total']>0:
                x.append(v)

    return x
    
#按照时间区间查询条线准开发需求数量/图表
@expose('/charts/requirements/count/graph')
def charts_requirements_count_graph():
    from uliweb.orm import get_model
    from uliweb.utils.generic import SimpleListView
    from uliweb import request

    begin_Date,end_Date = get_date()
    begin_month = begin_Date.month
    end_month = end_Date.month

    x = get_count_view_graph('tx')
    
    data = {}
    data['name'] = str(begin_month)+'至'+str(end_month)+'月各条线准开发需求数量分布' 
    data['data'] = x  
    response.template = 'charts_requirements_count_graph.xml'
    response.content_type = 'text/xml'

    result = {}
    result['begin_date'] = begin_Date
    result['end_date'] = end_Date
    result['data'] = data
    return result

#按照时间区间查询各中心准开发需求数量/图表
@expose('/charts/requirements/countzx/graph')
def charts_requirements_countzx_graph():
    from uliweb.orm import get_model
    from uliweb.utils.generic import SimpleListView
    from uliweb import request

    begin_Date,end_Date = get_date()
    begin_month = begin_Date.month
    end_month = end_Date.month

    x = get_count_view_graph('zx')
    
    data = {}
    data['name'] = str(begin_month)+'至'+str(end_month)+'月各中心准开发需求数量分布' 
    data['data'] = x  
    response.template = 'charts_requirements_countzx_graph.xml'
    response.content_type = 'text/xml'

    result = {}
    result['begin_date'] = begin_Date
    result['end_date'] = end_Date
    result['data'] = data
    return result


#按照时间区间查询各中心准开发需求数量/图表
@expose('/charts/requirements/countsys/graph')
def charts_requirements_countsys_graph():
    from uliweb.orm import get_model
    from uliweb.utils.generic import SimpleListView
    from uliweb import request

    begin_Date,end_Date = get_date()
    begin_month = begin_Date.month
    end_month = end_Date.month

    x = get_count_view_graph('sys')
    zx = get_model('kfzx')
    y = []
    for i in zx.all():
        j = [i.id,i.name]
        y.append(j)
    
    data = {}
    data['name'] = str(begin_month)+'至'+str(end_month)+'月各系统准开发需求数量分布' 
    data['data'] = x  
    data['label'] = y
    response.template = 'charts_requirements_countsys_graph.xml'
    response.content_type = 'text/xml'
    result = {}
    result['begin_date'] = begin_Date
    result['end_date'] = end_Date
    result['data'] = data
    return result


#需求数量涉及任务书分布
@expose('/charts/assignment/date/kfzx/graph')
def charts_assignment_date_kfzx_graph():
    from uliweb.orm import get_model
    from uliweb.utils.generic import SimpleListView
    from uliweb import request

    begin_Date,end_Date = get_date()
    begin_month = begin_Date.month
    end_month = end_Date.month
    
    #初始化data,
    data = {}
    stages = ['total','new_xm','new_zxm','new_fxm','old_xm','old_zxm','old_fxm']
    kfzx = get_model('kfzx')
    for obj in kfzx.all():
        data[obj.id] ={}
        for y in stages:
            data[obj.id][y] = 0
    ass = get_model('assignment')
    new = select([ass.c.kfzx,ass.c.type,func.count(ass.c.id)],and_(ass.c.release_date>=begin_Date,ass.c.release_date<=end_Date)).group_by(ass.c.kfzx,ass.c.type)
    old = select([ass.c.kfzx,ass.c.type,func.count(ass.c.id)],and_(ass.c.release_date<begin_Date,ass.c.end_date>begin_Date)).group_by(ass.c.kfzx,ass.c.type)    
    new_rs = new.execute()
    old_rs = old.execute()   
         
    for row in new_rs:
        zx, type, count = row
        type = settings.PARA.assignment_new_map[type]        
        data[int(zx)][type] += count
        data[int(zx)]['total'] += count
    
    for row in old_rs:
        zx, type, count = row
        type = settings.PARA.assignment_old_map[type]
        data[int(zx)][type] += count
        data[int(zx)]['total'] += count

    x = []
    for t, v in data.items():        
        v['zx_name'] = kfzx.get(t).name
        x.append(v)

    data = {}
    data['name'] = str(begin_month)+'至'+str(end_month)+'月各系统准开发需求数量分布' 
    data['data'] = x  
    response.template = 'charts_assignment_date_kfzx_graph.xml'
    response.content_type = 'text/xml'
    result = {}
    result['begin_date'] = begin_Date
    result['end_date'] = end_Date
    result['data'] = data
    return result
    
#任务书按系统分布
@expose('/charts/assignment/date/sys/graph')
def charts_assignment_date_sys_graph():
    from uliweb.orm import get_model
    from uliweb.utils.generic import SimpleListView
    from uliweb import request

    begin_Date,end_Date = get_date()
    begin_month = begin_Date.month
    end_month = end_Date.month
    
    #初始化data,
    sys = get_model('system')
    data = {}
    stages = ['zx','total','new_xm','new_zxm','new_fxm','old_xm','old_zxm','old_fxm']
    for obj in sys.all():
        data[obj.id] ={}
        for z in stages:
            data[obj.id][z] = 0
    
    ass = get_model('assignment')
    kfzx = get_model('kfzx')
    ass_sys = ass.systems.table
    user_zx = request.user._kfzx_
    if request.user.department=='2':        
        new = select([ass_sys.c.system_id,ass.c.kfzx,ass.c.type,func.count(ass.c.id)],
            and_(ass.c.release_date>=begin_Date,
            ass.c.release_date<=end_Date,
            ass.c.id==ass_sys.c.assignment_id,
            ass.c.kfzx==user_zx))\
            .group_by(ass_sys.c.system_id,ass.c.type)
        old = select([ass_sys.c.system_id,ass.c.kfzx,ass.c.type,func.count(ass.c.id)],
            and_(ass.c.release_date<begin_Date,
            ass.c.end_date>begin_Date,
            ass.c.id==ass_sys.c.assignment_id,
            ass.c.kfzx==user_zx))\
            .group_by(ass_sys.c.system_id,ass.c.type)    
    else:        
        new = select([ass_sys.c.system_id,ass.c.kfzx,ass.c.type,func.count(ass.c.id)],
            and_(ass.c.release_date>=begin_Date,
            ass.c.release_date<=end_Date,
            ass.c.id==ass_sys.c.assignment_id))\
            .group_by(ass_sys.c.system_id,ass.c.type)
        old = select([ass_sys.c.system_id,ass.c.kfzx,ass.c.type,func.count(ass.c.id)],
            and_(ass.c.release_date<begin_Date,
            ass.c.end_date>begin_Date,
            ass.c.id==ass_sys.c.assignment_id))\
            .group_by(ass_sys.c.system_id,ass.c.type)    
    new_rs = new.execute()
    old_rs = old.execute()   
                      
    y = []
    for row in new_rs:
        system, zx_id,type, count = row
        type = settings.PARA.assignment_new_map[type]        
        data[int(system)][type] += count
        data[int(system)]['total'] += count
        data[int(system)]['zx'] = zx_id
        zx_name = kfzx.get(zx_id).name
        if [zx_id,zx_name] not in y:
            y.append([zx_id,zx_name])
    
    for row in old_rs:
        system, zx_id,type, count = row
        type = settings.PARA.assignment_old_map[type]
        data[int(system)][type] += count
        data[int(system)]['total'] += count
        data[int(system)]['zx'] = zx_id
        zx_name = kfzx.get(zx_id).name
        if [zx_id,zx_name] not in y:
            y.append([zx_id,zx_name])
    
    x = []
    for t, v in data.items():        
        v['zx_name'] = sys.get(t).name
        if v['total']>0:
            x.append(v)
    data = {}
    data['name'] = str(begin_month)+'至'+str(end_month)+'月各系统任务书数量分布' 
    data['data'] = x  
    data['label'] = y
    response.template = 'charts_assignment_date_sys_graph.xml'
    response.content_type = 'text/xml'
    result = {}
    result['begin_date'] = begin_Date
    result['end_date'] = end_Date
    result['data'] = data
    return result


#按照时间区间查询各开发中心工作量/图表
@expose('/charts/resource/zx/date/graph')
def charts_resource_zx_date_graph():
    from uliweb.orm import get_model
    from uliweb.utils.generic import SimpleListView
    from uliweb import request

    begin_Date,end_Date = get_date()
    begin_month = begin_Date.month
    end_month = end_Date.month
    begin = get_yearmonth(begin_Date)
    end = get_yearmonth(end_Date)            
    
    task = get_model('tasks_details')
    zx = get_model('kfzx')
    sql = select([zx.c.name,
                func.sum(task.c.amount_inner_input),
                func.sum(task.c.amount_outer_input),
                func.sum(task.c.amount_inner_design),
                func.sum(task.c.amount_outer_design),
                func.sum(task.c.amount_inner_out8+task.c.amount_inner_weekend),
                func.sum(task.c.amount_outer_out8+task.c.amount_outer_weekend),                
                ],and_(task.c.year_month>=begin,
                task.c.year_month<=end,
                zx.c.id==task.c.kfzx)).group_by(zx.c.name)
    sql_rs = sql.execute()
    
    #生成数据
    x = []
    for row in sql_rs:
        name,zx_sum,gs_sum,zx_sum_design,gs_sum_design,zx_sum_extra,gs_sum_extra = row
        row = list(row)
        row.insert(1,zx_sum+gs_sum)
        x.append(row)
    data = {}
    data['name'] = str(begin_month)+'至'+str(end_month)+'月各中心工作量分布' 
    data['data'] = x  
    response.template = 'charts_resource_zx_date_graph.xml'
    response.content_type = 'text/xml'

    result = {}
    result['begin_date'] = begin_Date
    result['end_date'] = end_Date
    result['data'] = data
    return result


#各开发中心工作量月报
@expose('/charts/resource/zx/month/graph')
def charts_resource_zx_month_graph():
    from uliweb.orm import get_model
    from uliweb.utils.generic import SimpleListView
    from uliweb import request

    begin_Date,end_Date = get_date()
    begin_month = begin_Date.month
    end_month = end_Date.month
    begin = get_yearmonth(begin_Date)
    end = get_yearmonth(end_Date)            
    kfzx = get_model('kfzx')
    #初始化结构
    x = []
    a = get_month_list(begin_Date,end_Date)
    for i in a:
        j = {}
        j['month']=i
        j['total']=0
        j['total_zx']=0
        j['total_gs']=0
        for k in kfzx.all():
            j[str(k.id)+'_zx']=0
            j[str(k.id)+'_gs']=0
        x.append(j)
    #生成数据
    task = get_model('tasks_details')
    sql = select([task.c.year,task.c.month,task.c.kfzx,
                func.sum(task.c.amount_inner_input),
                func.sum(task.c.amount_outer_input),
                ],and_(task.c.year_month>=begin,
                task.c.year_month<=end,))\
                .group_by(task.c.year,task.c.month,task.c.kfzx)
    sql_rs = sql.execute()
    
    for row in sql_rs:
        year,month,zx,zx_sum,gs_sum = row
        for i in x:
            if i['month']==str(year)+'-'+str(month):
                i[str(zx)+'_zx']+=zx_sum
                i[str(zx)+'_gs']+=gs_sum
                i['total_zx']+=zx_sum
                i['total_gs']+=gs_sum
                i['total']+=zx_sum
                i['total']+=gs_sum
    #展示 
    y = []
    for k in kfzx.all():
        z =[0,0]
        z[0]=k.id
        z[1]=k.name
        y.append(z)
                    
    data = {}
    data['label'] = y
    data['name'] = str(begin_month)+'至'+str(end_month)+'月工作量分布' 
    data['data'] = x  
    response.template = 'charts_resource_zx_month_graph.xml'
    response.content_type = 'text/xml'
    
    result = {}
    result['begin_date'] = begin_Date
    result['end_date'] = end_Date
    result['data'] = data
    return result
    
    
    


#各开发中心工作量季报
@expose('/charts/resource/zx/season/graph')
def charts_resource_zx_season_graph():
    from uliweb.orm import get_model
    from uliweb.utils.generic import SimpleListView
    from uliweb import request
    
    season_map = {1:1,2:1,3:1,4:2,5:2,6:2,7:3,8:3,9:3,10:4,11:4,12:4}
    begin_Date,end_Date = get_date()
    begin_month = begin_Date.month
    end_month = end_Date.month
    begin = get_yearmonth(begin_Date)
    end = get_yearmonth(end_Date)            
    kfzx = get_model('kfzx')
    #初始化结构
    x = []
    a = get_season_list(begin_Date,end_Date)
    for i in a:
        j = {}
        j['month']=i
        j['total']=0
        j['total_zx']=0
        j['total_gs']=0        
        for k in kfzx.all():
            j[str(k.id)+'_zx']=0
            j[str(k.id)+'_gs']=0
        x.append(j)
    #生成数据
    task = get_model('tasks_details')
    sql = select([task.c.year,task.c.month,task.c.kfzx,
                func.sum(task.c.amount_inner_input),
                func.sum(task.c.amount_outer_input),
                ],and_(task.c.year_month>=begin,
                task.c.year_month<=end))\
                .group_by(task.c.year,task.c.month,task.c.kfzx)
    sql_rs = sql.execute()
    
    for row in sql_rs:
        year,month,zx,zx_sum,gs_sum = row
        for i in x:
            if i['month']==str(year)+'-'+str(season_map[month]):
                i[str(zx)+'_zx']+=zx_sum
                i[str(zx)+'_gs']+=gs_sum
                i['total_zx']+=zx_sum
                i['total_gs']+=gs_sum                
                i['total']+=zx_sum
                i['total']+=gs_sum
    #展示 
    y = []
    for k in kfzx.all():
        z =[0,0]
        z[0]=k.id
        z[1]=k.name
        y.append(z)
    data = {}
    data['label']=y
    data['name'] = str(begin_month)+'至'+str(end_month)+'月各季度工作量分布' 
    data['data'] = x  
    response.template = 'charts_resource_zx_season_graph.xml'
    response.content_type = 'text/xml'
    
    result = {}
    result['begin_date'] = begin_Date
    result['end_date'] = end_Date
    result['data'] = data
    return result
    

#按照时间区间查询各系统工作量
@expose('/charts/resource/sys/date/graph')
def charts_resource_sys_date_graph():
    from uliweb.orm import get_model
    from uliweb.utils.generic import SimpleListView
    from uliweb import request

    pageno = int(request.GET.get('pageno', 0))    
    begin_Date,end_Date = get_date()
    begin_month = begin_Date.month
    end_month = end_Date.month
    begin = get_yearmonth(begin_Date)
    end = get_yearmonth(end_Date)            
    
    task = get_model('tasks_details')
    sys = get_model('system')
    zx = get_model('kfzx')
    user_zx = request.user._kfzx_
    if request.user.department=='2':        
        sql = select([sys.c.name,task.c.kfzx,zx.c.name,
                    func.sum(task.c.amount_inner_input),
                    func.sum(task.c.amount_outer_input),
                    func.sum(task.c.amount_inner_design),
                    func.sum(task.c.amount_outer_design),
                    func.sum(task.c.amount_inner_out8+task.c.amount_inner_weekend),
                    func.sum(task.c.amount_outer_out8+task.c.amount_outer_weekend),],
                    and_(task.c.year_month>=begin,
                    task.c.year_month<=end,
                    sys.c.id==task.c.task_sys,zx.c.id==task.c.kfzx,
                    task.c.kfzx==user_zx))\
                    .group_by(sys.c.name)\
                    .order_by(func.sum(task.c.amount_inner_input)+func.sum(task.c.amount_outer_input))
    else:
        sql = select([sys.c.name,task.c.kfzx,zx.c.name,
                    func.sum(task.c.amount_inner_input),
                    func.sum(task.c.amount_outer_input),
                    func.sum(task.c.amount_inner_design),
                    func.sum(task.c.amount_outer_design),
                    func.sum(task.c.amount_inner_out8+task.c.amount_inner_weekend),
                    func.sum(task.c.amount_outer_out8+task.c.amount_outer_weekend),],
                    and_(task.c.year_month>=begin,
                    task.c.year_month<=end,
                    sys.c.id==task.c.task_sys,zx.c.id==task.c.kfzx))\
                    .group_by(sys.c.name)\
                    .order_by(func.sum(task.c.amount_inner_input)+func.sum(task.c.amount_outer_input))
        
    sql_rs = sql.execute()
    
    #生成数据
    x = []
    y = []
    for row in sql_rs:
        name,zx_id,zx_name,zx_sum,gs_sum,zx_sum_design,gs_sum_design,zx_sum_extra,gs_sum_extra = row
        row = list(row)
        row.insert(3,zx_sum+gs_sum)
        x.append(row)
        if [zx_id,zx_name] not in y:
            y.append([zx_id,zx_name])
    
    data = {}
    data['name'] = str(begin_month)+'至'+str(end_month)+'月各系统工作量分布' 
    data['data'] = x  
    data['label']=y
    response.template = 'charts_resource_sys_date_graph.xml'
    response.content_type = 'text/xml'
    
    result = {}
    result['begin_date'] = begin_Date
    result['end_date'] = end_Date
    result['data'] = data
    return result
    

#按照时间区间查询各条线需求消耗工作量
@expose('/charts/resource/tx/date/graph')
def charts_resource_tx_date_graph():
    from uliweb.orm import get_model
    from uliweb.utils.generic import SimpleListView
    from uliweb import request

    begin_Date,end_Date = get_date()
    begin_month = begin_Date.month
    end_month = end_Date.month
    begin = get_yearmonth(begin_Date)
    end = get_yearmonth(end_Date)            
    
    task = get_model('tasks_details')
    tx = get_model('tx')
    #01-05类任务，可对应到条线
    sql_develop = select([tx.c.name,
                func.sum(task.c.amount_inner_input),
                func.sum(task.c.amount_outer_input),
                func.sum(task.c.amount_inner_design),
                func.sum(task.c.amount_outer_design),
                func.sum(task.c.amount_inner_out8+task.c.amount_inner_weekend),
                func.sum(task.c.amount_outer_out8+task.c.amount_outer_weekend),],
                and_(task.c.year_month>=begin,
                task.c.year_month<=end,
                task.c.task_type.in_(['01','02','03','04','05']),
                tx.c.id==task.c.tx))\
                .group_by(tx.c.name)\
                .order_by(func.sum(task.c.amount_inner_input)+func.sum(task.c.amount_outer_input))
    sql_rs = sql_develop.execute()
    
    #其他任务，按类型分类
    sql_other = select([task.c.task_type,
                func.sum(task.c.amount_inner_input),
                func.sum(task.c.amount_outer_input),
                func.sum(task.c.amount_inner_design),
                func.sum(task.c.amount_outer_design),
                func.sum(task.c.amount_inner_out8+task.c.amount_inner_weekend),
                func.sum(task.c.amount_outer_out8+task.c.amount_outer_weekend),],
                and_(task.c.year_month>=begin,
                task.c.year_month<=end,
                ~task.c.task_type.in_(['01','02','03','04','05']),
                tx.c.id==task.c.tx))\
                .group_by(task.c.task_type)\
                .order_by(func.sum(task.c.amount_inner_input)+func.sum(task.c.amount_outer_input))
    sql_other_rs = sql_other.execute()
    
    #生成数据
    x = []
    for row in sql_rs:
        name,zx_sum,gs_sum,zx_sum_design,gs_sum_design,zx_sum_extra,gs_sum_extra = row
        row = list(row)
        row.insert(1,zx_sum+gs_sum)
        x.append(row)
    
    t = []
    
    for row in sql_other_rs:
        name,zx_sum,gs_sum,zx_sum_design,gs_sum_design,zx_sum_extra,gs_sum_extra = row        
        row = list(row)
        row.insert(1,zx_sum+gs_sum)
        for r in t:
            if r[0]==settings.PARA.task_type_map[name][1]:            
                for i in range(1,len(row)):
                    r[i]+=row[i]
            break
        
        s = [0]*len(settings.PARA.resource_tx_date)
        s[0]=settings.PARA.task_type_map[name][1]
        for i in range(1,len(row)):
            s[i]+=row[i]
        t.append(s)
    x += t
    
    data = {}
    data['name'] = str(begin_month)+'至'+str(end_month)+'月各条线工作量分布' 
    data['data'] = x  
    response.template = 'charts_resource_tx_date_graph.xml'
    response.content_type = 'text/xml'
    
    result = {}
    result['begin_date'] = begin_Date
    result['end_date'] = end_Date
    result['data'] = data
    return result
    
#各条线涉及需求工作量月报
@expose('/charts/resource/tx/month/graph')
def charts_resource_tx_month_graph():
    from uliweb.orm import get_model
    from uliweb.utils.generic import SimpleListView
    from uliweb import request

    begin_Date,end_Date = get_date()
    begin_month = begin_Date.month
    end_month = end_Date.month
    begin = get_yearmonth(begin_Date)
    end = get_yearmonth(end_Date)            
    tx = get_model('tx')
    #初始化结构
    x = []
    a = get_month_list(begin_Date,end_Date)
    for i in a:
        j = {}
        j['month']=i
        j['total']=0
        for k in tx.all():
            j[str(k.id)+'_zx']=0
            j[str(k.id)+'_gs']=0
        j['yw_zx']=0
        j['yw_gs']=0
        j['cs_zx']=0
        j['cs_gs']=0
        j['gg_zx']=0
        j['gg_gs']=0
        j['qt_zx']=0
        j['qt_gs']=0
        x.append(j)
    #初始化条线列表
    y = []
    for k in tx.all():
        y.append([str(k.id),k.name])
    y.append(['yw','运维'])
    y.append(['cs','测试'])
    y.append(['gg','公共'])
    y.append(['qt','其他'])
          
    #生成数据
    #01-05类任务，可对应到条线
    task = get_model('tasks_details')
    sql_develop = select([task.c.year,task.c.month,task.c.tx,
                func.sum(task.c.amount_inner_input),
                func.sum(task.c.amount_outer_input),],
                and_(task.c.year_month>=begin,
                task.c.year_month<=end,
                task.c.task_type.in_(['01','02','03','04','05']),
                tx.c.id==task.c.tx))\
                .group_by(task.c.year,task.c.month,task.c.tx)
                
    sql_rs = sql_develop.execute()
    
    #其他任务，按类型分类
    sql_other = select([task.c.year,task.c.month,task.c.task_type,
                func.sum(task.c.amount_inner_input),
                func.sum(task.c.amount_outer_input),],
                and_(task.c.year_month>=begin,
                task.c.year_month<=end,
                ~task.c.task_type.in_(['01','02','03','04','05']),
                tx.c.id==task.c.tx))\
                .group_by(task.c.year,task.c.month,task.c.task_type)
    sql_other_rs = sql_other.execute()
    
    for row in sql_rs:
        year,month,tx,zx_sum,gs_sum = row
        for i in x:
            if i['month']==str(year)+'-'+str(month):
                i[str(tx)+'_zx']=zx_sum
                i[str(tx)+'_gs']=gs_sum
                i['total']+=zx_sum
                i['total']+=gs_sum
    for row in sql_other_rs:
        year,month,type,zx_sum,gs_sum = row
        for i in x:
            if i['month']==str(year)+'-'+str(month):
                i[settings.PARA.task_type_map[type][0]+'_zx']=zx_sum
                i[settings.PARA.task_type_map[type][0]+'_gs']=gs_sum
                i['total']+=zx_sum
                i['total']+=gs_sum    
                    
    data = {}
    data['name'] = str(begin_month)+'至'+str(end_month)+'月各条线相关开发工作量分布' 
    data['data'] = x  
    data['label']=y
    response.template = 'charts_resource_tx_month_graph.xml'
    response.content_type = 'text/xml'
    
    result = {}
    result['begin_date'] = begin_Date
    result['end_date'] = end_Date
    result['data'] = data
    return result
    
#各条线涉及需求工作量季报
@expose('/charts/resource/tx/season/graph')
def charts_resource_tx_season_graph():
    from uliweb.orm import get_model
    from uliweb.utils.generic import SimpleListView
    from uliweb import request

    season_map = {1:1,2:1,3:1,4:2,5:2,6:2,7:3,8:3,9:3,10:4,11:4,12:4}
    begin_Date,end_Date = get_date()
    begin_month = begin_Date.month
    end_month = end_Date.month
    begin = get_yearmonth(begin_Date)
    end = get_yearmonth(end_Date)            
    tx = get_model('tx')
    #初始化结构
    x = []
    a = get_season_list(begin_Date,end_Date)
    for i in a:
        j = {}
        j['season']=i
        j['total']=0
        for k in tx.all():
            j[str(k.id)+'_zx']=0
            j[str(k.id)+'_gs']=0
        j['yw_zx']=0
        j['yw_gs']=0
        j['cs_zx']=0
        j['cs_gs']=0
        j['gg_zx']=0
        j['gg_gs']=0
        j['qt_zx']=0
        j['qt_gs']=0
        x.append(j)
    #初始化条线列表
    y = []
    for k in tx.all():
        y.append([str(k.id),k.name])
    y.append(['yw','运维'])
    y.append(['cs','测试'])
    y.append(['gg','公共'])
    y.append(['qt','其他'])
          
    #生成数据
    #01-05类任务，可对应到条线
    task = get_model('tasks_details')
    sql_develop = select([task.c.year,task.c.month,task.c.tx,
                func.sum(task.c.amount_inner_input),
                func.sum(task.c.amount_outer_input),],
                and_(task.c.year_month>=begin,
                task.c.year_month<=end,
                task.c.task_type.in_(['01','02','03','04','05']),
                tx.c.id==task.c.tx))\
                .group_by(task.c.year,task.c.month,task.c.tx)
                
    sql_rs = sql_develop.execute()
    
    #其他任务，按类型分类
    sql_other = select([task.c.year,task.c.month,task.c.task_type,
                func.sum(task.c.amount_inner_input),
                func.sum(task.c.amount_outer_input),],
                and_(task.c.year_month>=begin,
                task.c.year_month<=end,
                ~task.c.task_type.in_(['01','02','03','04','05']),
                tx.c.id==task.c.tx))\
                .group_by(task.c.year,task.c.month,task.c.task_type)
    sql_other_rs = sql_other.execute()
    
    for row in sql_rs:
        year,month,zx,zx_sum,gs_sum = row
        for i in x:
            if i['season']==str(year)+'-'+str(season_map[month]):
                i[str(zx)+'_zx']+=zx_sum
                i[str(zx)+'_gs']+=gs_sum
                i['total']+=zx_sum
                i['total']+=gs_sum
    for row in sql_other_rs:
        year,month,type,zx_sum,gs_sum = row
        for i in x:
            if i['season']==str(year)+'-'+str(season_map[month]):
                i[settings.PARA.task_type_map[type][0]+'_zx']=zx_sum
                i[settings.PARA.task_type_map[type][0]+'_gs']=gs_sum
                i['total']+=zx_sum
                i['total']+=gs_sum
                    
    data = {}
    data['name'] = str(begin_month)+'至'+str(end_month)+'月各季度条线相关开发工作量分布' 
    data['data'] = x  
    data['label']=y
    response.template = 'charts_resource_tx_season_graph.xml'
    response.content_type = 'text/xml'
    
    result = {}
    result['begin_date'] = begin_Date
    result['end_date'] = end_Date
    result['data'] = data
    return result
    
#按照时间区间查询各阶段工作量
@expose('/charts/resource/stage/date/graph')
def charts_resource_stage_date_graph():
    from uliweb.orm import get_model
    from uliweb.utils.generic import SimpleListView
    from uliweb import request

    begin_Date,end_Date = get_date()
    begin_month = begin_Date.month
    end_month = end_Date.month
    begin = get_yearmonth(begin_Date)
    end = get_yearmonth(end_Date)            
    
    task = get_model('tasks_details')
    user_zx = request.user._kfzx_
    if request.user.department=='2':
        sql = select([task.c.stage,
                    func.sum(task.c.amount_inner_input),
                    func.sum(task.c.amount_outer_input),
                    func.sum(task.c.amount_inner_design),
                    func.sum(task.c.amount_outer_design),
                    func.sum(task.c.amount_inner_out8+task.c.amount_inner_weekend),
                    func.sum(task.c.amount_outer_out8+task.c.amount_outer_weekend),],
                    and_(task.c.year_month>=begin,
                    task.c.year_month<=end,
                    task.c.task_type.in_(['01','02','03','04']),
                    ~task.c.stage.in_(['00','FN']),
                    task.c.kfzx==user_zx))\
                    .group_by(task.c.stage)\
                    .order_by(task.c.stage)
        sql_ky = select([task.c.task_type,
                    func.sum(task.c.amount_inner_input),
                    func.sum(task.c.amount_outer_input),
                    func.sum(task.c.amount_inner_design),
                    func.sum(task.c.amount_outer_design),
                    func.sum(task.c.amount_inner_out8+task.c.amount_inner_weekend),
                    func.sum(task.c.amount_outer_out8+task.c.amount_outer_weekend),],
                    and_(task.c.year_month>=begin,
                    task.c.year_month<=end,
                    task.c.task_type=='05',
                    task.c.kfzx==user_zx))\
                    .group_by(task.c.task_type)
    else:
        sql = select([task.c.stage,
                    func.sum(task.c.amount_inner_input),
                    func.sum(task.c.amount_outer_input),
                    func.sum(task.c.amount_inner_design),
                    func.sum(task.c.amount_outer_design),
                    func.sum(task.c.amount_inner_out8+task.c.amount_inner_weekend),
                    func.sum(task.c.amount_outer_out8+task.c.amount_outer_weekend),],
                    and_(task.c.year_month>=begin,
                    task.c.year_month<=end,
                    task.c.task_type.in_(['01','02','03','04']),
                    ~task.c.stage.in_(['00','FN'])))\
                    .group_by(task.c.stage)\
                    .order_by(task.c.stage)
        sql_ky = select([task.c.task_type,
                    func.sum(task.c.amount_inner_input),
                    func.sum(task.c.amount_outer_input),
                    func.sum(task.c.amount_inner_design),
                    func.sum(task.c.amount_outer_design),
                    func.sum(task.c.amount_inner_out8+task.c.amount_inner_weekend),
                    func.sum(task.c.amount_outer_out8+task.c.amount_outer_weekend),],
                    and_(task.c.year_month>=begin,
                    task.c.year_month<=end,
                    task.c.task_type=='05'))\
                    .group_by(task.c.task_type)
    
    sql_rs = sql.execute()    
    sql_ky_rs = sql_ky.execute()    
    #生成数据
    x = []
    map_stage = dict(settings.PARA.TASK_STAGE)
    for row in sql_rs:
        name,zx_sum,gs_sum,zx_sum_design,gs_sum_design,zx_sum_extra,gs_sum_extra = row
        row = list(row)
        row[0]= map_stage[name]
        row.insert(1,zx_sum+gs_sum)
        x.append(row)
    #插入可研阶段数据
    for row in sql_ky_rs:
        name,zx_sum,gs_sum,zx_sum_design,gs_sum_design,zx_sum_extra,gs_sum_extra = row
        row = list(row)
        row[0]='可研'
        row.insert(1,zx_sum+gs_sum)
        x.insert(0,row)

    data = {}
    data['name'] = str(begin_month)+'至'+str(end_month)+'月各阶段工作量分布' 
    data['data'] = x  
    response.template = 'charts_resource_stage_date_graph.xml'
    response.content_type = 'text/xml'
    
    result = {}
    result['begin_date'] = begin_Date
    result['end_date'] = end_Date
    result['data'] = data
    return result
    


#各阶段工作量月报
@expose('/charts/resource/stage/month/graph')
def charts_resource_stage_month_graph():
    from uliweb.orm import get_model
    from uliweb.utils.generic import SimpleListView
    from uliweb import request

    begin_Date,end_Date = get_date()
    begin_month = begin_Date.month
    end_month = end_Date.month
    begin = get_yearmonth(begin_Date)
    end = get_yearmonth(end_Date)            
    #初始化结构
    x = []
    a = get_month_list(begin_Date,end_Date)
    for i in a:
        j = {}
        j['month']=i
        j['total']=0
        j['ky_zx']=0
        j['ky_gs']=0
        for k in settings.PARA.TASK_STAGE:
            if k[0] not in ['00','FN']:                
                j[k[0]+'_zx']=0
                j[k[0]+'_gs']=0
        x.append(j)
    y = []
    y.append(['ky','可研'])
    for k in settings.PARA.TASK_STAGE:
        if k[0] not in ['00','FN']:                
            y.append(list(k))
    
    #生成数据
    task = get_model('tasks_details')
    user_zx = request.user._kfzx_
    if request.user.department=='2':
        sql = select([task.c.year,task.c.month,task.c.stage,
                    func.sum(task.c.amount_inner_input),
                    func.sum(task.c.amount_outer_input),
                    ],and_(task.c.year_month>=begin,
                    task.c.year_month<=end,
                    task.c.task_type.in_(['01','02','03','04']),
                    ~task.c.stage.in_(['00','FN']),
                    task.c.kfzx==user_zx))\
                    .group_by(task.c.year,task.c.month,task.c.stage)
        sql_ky = select([task.c.year,task.c.month,
                    func.sum(task.c.amount_inner_input),
                    func.sum(task.c.amount_outer_input),],
                    and_(task.c.year_month>=begin,
                    task.c.year_month<=end,
                    task.c.task_type=='05',
                    task.c.kfzx==user_zx))\
                    .group_by(task.c.year,task.c.month)
    else:
        sql = select([task.c.year,task.c.month,task.c.stage,
                    func.sum(task.c.amount_inner_input),
                    func.sum(task.c.amount_outer_input),
                    ],and_(task.c.year_month>=begin,
                    task.c.year_month<=end,
                    task.c.task_type.in_(['01','02','03','04']),
                    ~task.c.stage.in_(['00','FN'])))\
                    .group_by(task.c.year,task.c.month,task.c.stage)
        sql_ky = select([task.c.year,task.c.month,
                    func.sum(task.c.amount_inner_input),
                    func.sum(task.c.amount_outer_input),],
                    and_(task.c.year_month>=begin,
                    task.c.year_month<=end,
                    task.c.task_type=='05'))\
                    .group_by(task.c.year,task.c.month)
    
    sql_rs = sql.execute()
    sql_ky_rs = sql_ky.execute()
    
    
    for row in sql_rs:
        year,month,zx,zx_sum,gs_sum = row
        for i in x:
            if i['month']==str(year)+'-'+str(month):
                i[str(zx)+'_zx']+=zx_sum
                i[str(zx)+'_gs']+=gs_sum
                i['total']+=zx_sum
                i['total']+=gs_sum
    
    for row in sql_ky_rs:
        year,month,zx_sum,gs_sum = row
        for i in x:
            if i['month']==str(year)+'-'+str(month):
                i['ky_zx']+=zx_sum
                i['ky_gs']+=gs_sum
                i['total']+=zx_sum
                i['total']+=gs_sum

    data = {}
    data['name'] = str(begin_month)+'至'+str(end_month)+'月各阶段开发工作量分布' 
    data['data'] = x  
    data['label']=y
    response.template = 'charts_resource_tx_month_graph.xml'
    response.content_type = 'text/xml'
    
    result = {}
    result['begin_date'] = begin_Date
    result['end_date'] = end_Date
    result['data'] = data
    return result
    
#各阶段工作量季报
@expose('/charts/resource/stage/season/graph')
def charts_resource_stage_season_graph():
    from uliweb.orm import get_model
    from uliweb.utils.generic import SimpleListView
    from uliweb import request

    season_map = {1:1,2:1,3:1,4:2,5:2,6:2,7:3,8:3,9:3,10:4,11:4,12:4}
    begin_Date,end_Date = get_date()
    begin_month = begin_Date.month
    end_month = end_Date.month
    begin = get_yearmonth(begin_Date)
    end = get_yearmonth(end_Date)            
    #初始化结构
    x = []
    a = get_season_list(begin_Date,end_Date)
    for i in a:
        j = {}
        j['season']=i
        j['total']=0
        j['ky_zx']=0
        j['ky_gs']=0
        for k in settings.PARA.TASK_STAGE:
            if k[0] not in ['00','FN']:                
                j[k[0]+'_zx']=0
                j[k[0]+'_gs']=0
        x.append(j)
    y = []
    y.append(['ky','可研'])
    for k in settings.PARA.TASK_STAGE:
        if k[0] not in ['00','FN']:                
            y.append(list(k))
    
    #生成数据
    task = get_model('tasks_details')
    user_zx = request.user._kfzx_
    if request.user.department=='2':
        sql = select([task.c.year,task.c.month,task.c.stage,
                    func.sum(task.c.amount_inner_input),
                    func.sum(task.c.amount_outer_input),
                    ],and_(task.c.year_month>=begin,
                    task.c.year_month<=end,
                    task.c.task_type.in_(['01','02','03','04']),
                    ~task.c.stage.in_(['00','FN']),
                    task.c.kfzx==user_zx))\
                    .group_by(task.c.year,task.c.month,task.c.stage)
        sql_ky = select([task.c.year,task.c.month,
                    func.sum(task.c.amount_inner_input),
                    func.sum(task.c.amount_outer_input),],
                    and_(task.c.year_month>=begin,
                    task.c.year_month<=end,
                    task.c.task_type=='05',
                    task.c.kfzx==user_zx))\
                    .group_by(task.c.year,task.c.month)
    else:
        sql = select([task.c.year,task.c.month,task.c.stage,
                    func.sum(task.c.amount_inner_input),
                    func.sum(task.c.amount_outer_input),
                    ],and_(task.c.year_month>=begin,
                    task.c.year_month<=end,
                    task.c.task_type.in_(['01','02','03','04']),
                    ~task.c.stage.in_(['00','FN'])))\
                    .group_by(task.c.year,task.c.month,task.c.stage)
        sql_ky = select([task.c.year,task.c.month,
                    func.sum(task.c.amount_inner_input),
                    func.sum(task.c.amount_outer_input),],
                    and_(task.c.year_month>=begin,
                    task.c.year_month<=end,
                    task.c.task_type=='05'))\
                    .group_by(task.c.year,task.c.month)
    
    sql_rs = sql.execute()
    sql_ky_rs = sql_ky.execute()
    
    
    for row in sql_rs:
        year,month,zx,zx_sum,gs_sum = row
        for i in x:
            if i['season']==str(year)+'-'+str(season_map[month]):
                i[str(zx)+'_zx']+=zx_sum
                i[str(zx)+'_gs']+=gs_sum
                i['total']+=zx_sum
                i['total']+=gs_sum
            if i['season']==u'总计':
                i[str(zx)+'_zx']+=zx_sum                
                i[str(zx)+'_gs']+=gs_sum                
                i['total']+=zx_sum
                i['total']+=gs_sum
    
    for row in sql_ky_rs:
        year,month,zx_sum,gs_sum = row
        for i in x:
            if i['season']==str(year)+'-'+str(season_map[month]):
                i['ky_zx']+=zx_sum
                i['ky_gs']+=gs_sum
                i['total']+=zx_sum
                i['total']+=gs_sum
            if i['season']==u'总计':
                i['ky_zx']+=zx_sum                
                i['ky_gs']+=gs_sum                
                i['total']+=zx_sum
                i['total']+=gs_sum

    data = {}
    data['name'] = str(begin_month)+'至'+str(end_month)+'月各阶段开发工作量分布' 
    data['data'] = x  
    data['label']=y
    response.template = 'charts_resource_tx_season_graph.xml'
    response.content_type = 'text/xml'
    
    result = {}
    result['data'] = data
    return result
    
#系统自主研发率报表
@expose('/charts/resource/independent/sys/date/graph')
def charts_resource_independent_sys_date_graph():
    from uliweb.orm import get_model
    from uliweb.utils.generic import SimpleListView
    from uliweb import request

    begin_Date,end_Date = get_date()
    begin_month = begin_Date.month
    end_month = end_Date.month
    begin = get_yearmonth(begin_Date)
    end = get_yearmonth(end_Date)            
    month_list = get_month_list(begin_Date,end_Date)
    kfzx = get_model('kfzx')
    sys = get_model('system')
    selfdev = get_model('selfdev')
    resource = get_model('resource')
    #查询系统对应的资源数
    sql = select([sys.c.kfzx,sys.c.sys_id,sys.c.name,
                selfdev.c.qualified,selfdev.c.complexity,
                func.sum(resource.c.inner_number),
                func.sum(resource.c.outter_number),
                selfdev.c.independent_lastyear,
                selfdev.c.independent_thisyear,
                selfdev.c.independent_destination,
                selfdev.c.system],
                and_(resource.c.year_month>=begin,
                resource.c.year_month<=end,sys.c.id==resource.c.system,
                sys.c.id==selfdev.c.system),)\
                .group_by(sys.c.sys_id)\
                .order_by(sys.c.sys_id)
    sql_rs = sql.execute()
    
    #查询任务书数量
    ass = get_model('assignment')
    ass_sys = ass.systems.table
    ass = select([ass_sys.c.system_id,ass.c.type,func.count(ass.c.id)],
        or_(and_(ass.c.release_date<begin_Date,
        ass.c.end_date>begin_Date,
        ass.c.id==ass_sys.c.assignment_id),
        and_(ass.c.release_date>=begin_Date,
        ass.c.release_date<=end_Date,
        ass.c.id==ass_sys.c.assignment_id)))\
        .group_by(ass_sys.c.system_id,ass.c.type)    
    ass_rs = ass.execute()
    #生成数据
    x = []
    i = [0]*23
    for row in sql_rs:
        zx,sys_abbr,sys_name,qualified,complexity,inner_number,\
        outter_number,lastyear,thisyear,destination,sys_id = row
        row = list(row)
        #中心
        i[0] = kfzx.get(zx).name
        #系统编号
        i[1] = sys_abbr
        #系统名称
        i[2] = sys_name
        #是否当年达标系统
        i[3] = qualified
        #复杂度
        i[4] = complexity
        for ass_row in ass_rs:            
            sys,type,num=ass_row
            if (sys==sys_id)and(int(type) in [1,2]):
                #项目数量
                i[5]=num
            if (sys==sys_id)and(int(type) == 3):    
                #非项目数量
                i[6]=num
        #总投入
        i[7]=inner_number+outter_number
        #中心投入
        i[8]=inner_number
        #公司投入
        i[9]=outter_number
        #工作量级别
        i[10]=get_workload_level((inner_number+outter_number)/len(month_list))
        #自主率
        i[11] = round(float(inner_number)*100/(inner_number+outter_number),2)
        #研发类型
        i[12] = get_develop_type(i[11])
        #和上年底比较/目标自主率
        i[13] = lastyear
        #和上年底比较/目标研发类型
        i[14] = get_develop_type(int(lastyear))
        #和上年底比较/自主率差值
        i[15] = i[11]-int(lastyear)
        #和上年底比较/类型是否达标
        if (i[15]<0)and(i[14]!=i[12]):
            i[16]='否'
        else:
            i[16]='是'
        #和本年底比较/目标研发类型
        i[17] = thisyear
        #和上年底比较/目标研发类型
        i[18] = get_develop_type(int(thisyear))
        #和上年底比较/自主率差值
        i[19] = i[11]-int(thisyear)
        #和上年底比较/类型是否达标
        if (i[19]<0)and(i[18]!=i[12]):
            i[20]='否'
        else:
            i[20]='是'
        #和最终目标比较/目标研发类型
        i[21] = destination
        #和最终目标比较/类型是否达标
        if ((i[11]-int(destination))<0)and(i[21]!=i[12]):
            i[22]='否'
        else:
            i[22]='是'
        
        x.append(i)

    data = {}
    data['name'] = str(begin_month)+'至'+str(end_month)+'月自主研发情况' 
    data['data'] = x
    response.template = 'charts_resource_independent_sys_date_graph.xml'
    response.content_type = 'text/xml'

    result = {}
    result['data'] = data
    return result

#中心自主研发率报表
@expose('/charts/resource/independent/zx/date/graph')
def charts_resource_independent_zx_date_graph():
    from uliweb.orm import get_model
    from uliweb.utils.generic import SimpleListView
    from uliweb import request

    begin_Date,end_Date = get_date()
    begin_month = begin_Date.month
    end_month = end_Date.month
    begin = get_yearmonth(begin_Date)
    end = get_yearmonth(end_Date)            
    kfzx = get_model('kfzx')
    selfdev_zx = get_model('selfdev_zx')
    resource = get_model('resource')
    #查询系统对应的资源数
    sql = select([resource.c.system,resource.c.kfzx,kfzx.c.name,
                func.sum(resource.c.inner_number),
                func.sum(resource.c.outter_number)],
                and_(resource.c.year_month>=begin,
                resource.c.year_month<=end,
                resource.c.kfzx==kfzx.c.id),)\
                .group_by(resource.c.system)\
                .order_by(resource.c.system)
    sql_rs = sql.execute()
    
    #初始化中心自主率
    y = []
    for row in selfdev_zx.all().order_by(selfdev_zx.c.kfzx):
        j = [0]*11
        j[0] = row.kfzx.name
        j[1] = row.initial
        j[2] = row.lastyear
        j[3] = row.thisyear
        j[4] = row.destination
        y.append(j)
    #生成数据
    for row in sql_rs:
        sys,zx,zx_name,inner_number,outter_number = row
        #计算自主率
        if inner_number+outter_number>0:            
            x = round(float(inner_number)*100/(inner_number+outter_number),2)
        else:
            x = 0
        
        for i in y:
            if i[0]==zx_name:
                i[9]+=inner_number
                i[10]+=outter_number
                #自主率求和
                i[7]+=x
                #系统个数
                i[8]+=1
                #平均自主率
                if i[8]>0:
                    i[5]=round(float(i[7])/i[8],2)
                #总体自主率
                if i[9]+i[10]>0:
                    i[6]=round(float(i[9])*100/(i[9]+i[10]),2)
            
    data = {}
    data['name'] = str(begin_month)+'至'+str(end_month)+'月自主研发情况' 
    data['data'] = y
    response.template = 'charts_resource_independent_zx_date_graph.xml'
    response.content_type = 'text/xml'

    result = {}
    result['data'] = data
    return result
